Securing MS SQL Server

Follow these guidelines for securing MS SQL server:

  • Harden the Windows server where SQL server operates against standard industry best practices baselines like CIS Benchmark and ISO 20001 to avoid any data breach.

  • Install only the required SQL database components.

  • Limit the permissions of service accounts according to the Principle of Least Privilege.

    Each SQL Server service is configured to run under a specified Windows or Active Directory account. You should define the permissions to run the services, based on the Principle of Least Privilege, which states that each account should have the minimum permissions and system rights it needs to function.

    • Active Directory Managed service account — This is usually the best option, for two reasons. First, since you cannot use managed service accounts to log on to a server, they are more secure than domain user accounts. Second, you do not need to manually manage password resets for service accounts, as you must for regular domain user accounts.

    • Domain User account — This is most common type of account used to run services. This account type is quite secure in a domain environment as it doesn’t have administrator privileges.

    • Local User account — This is a good choice for non-domain environments.

    • Local System account — These accounts are highly privileged so you should avoid using them to run services.

    • Network Service account — This type of account has fewer privileges than a system account, but it does enable a service to have access to network resources, so you should avoid using it whenever possible.

    • Virtual Service account — A virtual service account is similar to an AD managed service account, but it is a type of local account that you can use to manage services without a domain. It is simply an instance of the built-in Network Service account with its own unique identifier. Virtual service accounts are great to use for SQL services.

  • Turn off the SQL Server Browser service.

  • Use Groups and Roles to simplify management of Effective Permissions

    The effective permissions for a given account on a specific resource result from:

    • Explicit permissions granted directly to the account on the resource.

    • Permissions inherited from membership in a role or group.

    • Permissions inherited from a parent resource.

  • Follow the Principle of Least Privilege while assigning SQL server roles.

    Following are the server roles and their default permissions:

    • Sysadmin — Perform any activity on the SQL server.

    • Serveradmin — Configure SQL server settings and shut down the server.

    • Securityadmin — Manage logins, including their properties, passwords and permissions.

    • Processadmin — Terminate processes on the SQL Server instance.

    • Setupadmin — Add or remove linked servers and manage replication.

    • Bulkadmin — Execute the BULK INSERT statement.

    • Diskadmin — Manage disk files.

    • Dbcreator — Create, alter or drop any database.

    • Public — Every user is a member of this role. It does not have any permissions except to the objects that are configured as public.

  • Strong passwords are a must for all database administrator accounts to make them resistant to brute-force attacks. Following are the few recommendations to be considered while creating a new password:

    • Password should contain at least 10 characters.

    • It should contain uppercase and lowercase letters, numbers and symbols;

    • Passphrases are the best choice.

  • Use appropriate authentication options. Following are the available options:

    • SQL Server Authentication mode

    • Windows Authentication mode

    • SQL Server and Windows Authentication mode

    Is it recommended not to use Kerberos as an authentication protocol as it may affect the on premise deployments.

  • Monitor activities in your SQL server.

    Concurrency issues — If multiple users attempt to access the same data, some requests are blocked until others complete, which can result in deadlock, in which two operations are blocking one another. In such case, Inform the respective users and take necessary action.

    Deviations from your baseline — Record regular workloads metrics to establish a baseline so you know what they look like when the SQL database is operating normally. Then watch for deviations from that baseline. If you experience a significant change from your baseline without a clear cause, begin a security investigation as soon as possible.

  • You should always audit failed logins to SQL server. Once you have enabled login auditing in SQL Server, the failed and successful login information will be recorded to the SQL Server error logs, which should be monitored regularly for suspicious activities.

  • Protect against SQL Injection attacks.: The best way to protect against these attacks is to parameterize every query sent to the database. You should use properly configured storage procedures, they are much safer than direct dynamic SQL. Never pass string values in the front-end application, and be sure that all queries to the database are sanitized before being executed against the database.

  • Use encryption wisely.

    Encrypting data helps keep it secure even if unauthorized users gain access to it. There are several encryption features in SQL server which you can use to protect your data. They are:

    • Transparent data encryption (TDE)

    • Always encrypted- MS-SQL server 2016 or later. This will provide the best security.

    • Column level encryption